﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using MVCSoict.Models;
using Newtonsoft.Json;
using System.IO;
using OfficeOpenXml;

namespace MVCSoict.Controllers
{
    public class LessonController : Controller
    {
        private staffsoictEntities1 db = new staffsoictEntities1();
        private JsonSerializerSettings jsonSettings = new JsonSerializerSettings()
        {
            PreserveReferencesHandling = PreserveReferencesHandling.Objects
        };
        // GET: /Lesson/
        public ActionResult Index()
        {
            var lessons = db.Lessons.Include(l => l.User);
            return View(lessons.ToList());
        }

        public string Select(string keyword, int page, int pagesize)
        {
            string jsonResult = string.Empty;
            if (Session["username"] != null)
            {
                if (MVCSoict.Models.Authentication.Author.Authorize(Session["username"].ToString(), "lesson_select"))
                {
                    using (var context = new staffsoictEntities1())
                    {
                        var lessions = from l in context.Lessons
                                       where l.cname.Contains(keyword) || l.details.Contains(keyword) || l.room.Contains(keyword)
                                       orderby l.lid
                                       select l;

                        var pageData = lessions.Skip((page - 1) * pagesize).Take(pagesize).ToList();
                        foreach (var less in pageData)
                        {
                            jsonResult += JsonConvert.SerializeObject(less, jsonSettings);
                        }
                    }
                }
            }
            return jsonResult;
        }

        public string Add([Bind(Include = "uid,starttime,endtime,cid,cname,room,details,lid,sid")] Lesson lesson)
        {
            string res = string.Empty;
            if (Session["username"] != null)
            {
                if (MVCSoict.Models.Authentication.Author.Authorize(Session["username"].ToString(), "lesson_add"))
                {
                    try
                    {
                        db.Lessons.Add(lesson);
                        db.SaveChanges();
                        res = JsonConvert.SerializeObject(lesson, jsonSettings);
                    }
                    catch (Exception ex)
                    {
                        res = ex.Message;
                    }
                }
            }
            return res;
        }

        //tested
        public string Update([Bind(Include = "uid,starttime,endtime,cid,cname,room,details,lid,sid")] Lesson lesson)
        {
            string res = string.Empty;
            if (Session["username"] != null)
            {
                if (MVCSoict.Models.Authentication.Author.Authorize(Session["username"].ToString(), "lesson_update"))
                {
                    try
                    {
                        db.Entry(lesson).State = EntityState.Modified;
                        db.SaveChanges();
                        res = JsonConvert.SerializeObject(lesson, jsonSettings);
                    }
                    catch (Exception ex)
                    {
                        res = ex.Message;
                    }
                    return res;
                }
            }
        }

        public string Get(string id)
        {
            string res = string.Empty;
            try
            {
                var lessons = from l in db.Lessons
                              where l.lid.Equals(id)
                              select l;
                if ((lessons.ToList<Lesson>().Count() > 0))
                    res = JsonConvert.SerializeObject(lessons.ToList<Lesson>()[0], jsonSettings);
            }
            catch (Exception ex)
            {
                res = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath">đường dẫn đến file excel</param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string ReadFile(string filePath, string username)
        {
            string res = string.Empty;
            try
            {
                //string fileP = @"\Content\Schedule\schedule.xlsx";
                FileInfo file = new FileInfo(filePath);
                ExcelPackage excel = new ExcelPackage(file);
                excel.DebugMode = true;
                ExcelWorksheet workSheet = excel.Workbook.Worksheets[1];
                int row = workSheet.defaultRowHeight;
                int col = 7;
                for (int i = 2; i < (row + 2); i++)
                {
                    Lesson lesson = new Lesson();
                    lesson.uid = username;
                    lesson.lid = workSheet.Cell(i, 1).ToString();
                    lesson.room = workSheet.Cell(i, 2).ToString();
                    lesson.cname = workSheet.Cell(i, 3).ToString();
                    lesson.cid = workSheet.Cell(i, 4).ToString();
                    lesson.starttime = DateTime.Parse(workSheet.Cell(i, 5).ToString());
                    lesson.endtime = DateTime.Parse(workSheet.Cell(i, 6).ToString());
                    lesson.details = workSheet.Cell(i, 7).ToString();
                    this.Add(lesson);
                }
                res = "Added Lessons";
            }
            catch (Exception ex)
            {
                res = ex.Message;
            }
            return res;
        }

        // GET: /Lesson/Details/5
        public ActionResult Details(string id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Lesson lesson = db.Lessons.Find(id);
            if (lesson == null)
            {
                return HttpNotFound();
            }
            return View(lesson);
        }

        // GET: /Lesson/Create
        public ActionResult Create()
        {
            ViewBag.uid = new SelectList(db.Users, "username", "password");
            return View();
        }

        // POST: /Lesson/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include = "uid,starttime,endtime,cid,cname,room,details,lid,sid")] Lesson lesson)
        {
            if (ModelState.IsValid)
            {
                db.Lessons.Add(lesson);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            ViewBag.uid = new SelectList(db.Users, "username", "password", lesson.uid);
            return View(lesson);
        }

        // GET: /Lesson/Edit/5
        public ActionResult Edit(string id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Lesson lesson = db.Lessons.Find(id);
            if (lesson == null)
            {
                return HttpNotFound();
            }
            ViewBag.uid = new SelectList(db.Users, "username", "password", lesson.uid);
            return View(lesson);
        }

        // POST: /Lesson/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include = "uid,starttime,endtime,cid,cname,room,details,lid,sid")] Lesson lesson)
        {
            if (ModelState.IsValid)
            {
                db.Entry(lesson).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.uid = new SelectList(db.Users, "username", "password", lesson.uid);
            return View(lesson);
        }

        // GET: /Lesson/Delete/5
        public ActionResult Delete(string id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Lesson lesson = db.Lessons.Find(id);
            if (lesson == null)
            {
                return HttpNotFound();
            }
            return View(lesson);
        }

        // POST: /Lesson/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(string id)
        {
            Lesson lesson = db.Lessons.Find(id);
            db.Lessons.Remove(lesson);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}
